package zjs.dc.controller;

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

/**
 * @Class:
 * @Author: kongcb
 * @Description:  针对企查查t_eci_company表和中数t_ent_basic表进行数据映射开发
 * @Date: 2021/12/7 12:51
 */
public class TEciCompany {
    public static void main(String[] args) {
        SparkSession spark = SparkSession.builder().appName("t_eci_company").getOrCreate();
        spark.sparkContext().setLogLevel("ERROR");
        Dataset<Row> zs_data = spark.sql("select n.* from (select " +
                "c.*,row_number() over(partition by c.entid order by c.jobid desc) as num from (" +
                "select b.* from a_dc_ep_ods.bl_zs_t_ent_basic as b " +
                "union all " +
                "select a.* from a_dc_ep_ods.zs_t_ent_basic as a" +
                ") as c ) as n where n.num = 1");
        long zs_count = zs_data.count();
        if(zs_count>0){
            zs_data.drop("num").createOrReplaceTempView("zs_company");
            spark.sql("SELECT a.*,\n" +
                    "       b.tax_no,\n" +
                    "       b.org_no,\n" +
                    "       b.city,\n" +
                    "       b.county,\n" +
                    "       b.english_name,\n" +
                    "       b.original_name,\n" +
                    "       b.image_url,\n" +
                    "       b.email,\n" +
                    "       b.website,\n" +
                    "       d.industry_code as industryphy,\n" +
                    "       d.industry as industryphyname,\n" +
                    "       d.sub_industry_code,\n" +
                    "       d.sub_industry,\n" +
                    "       d.middle_category_code,\n" +
                    "       d.middle_category,\n" +
                    "       d.small_category_code,\n" +
                    "       d.small_category,\n" +
                    "       c.lat,\n" +
                    "       c.lng\n" +
                    "FROM a_dc_ep_ods.t_eci_company a\n" +
                    "LEFT JOIN a_dc_ep_dwi.t_eci_companyattr b ON a.company_id = b.company_id " +
                    "LEFT JOIN a_dc_ep_dwi.t_company_location c ON a.company_id = c.company_id " +
                    "LEFT JOIN a_dc_ep_dwi.t_eci_industry d ON a.company_id = d.company_id ").createOrReplaceTempView("tmp_sum_company");
            /**
             * t_eci_company
             */
            //获取中数增量数据表中的更新数据（inner join）
            spark.sql("SELECT b.entid as key_no,\n" +
                    " b.entid," +
                    "       a.company_id,\n" +
                    "       b.entname,\n" +
                    "       if(trim(b.frname) == trim(a.oper_name),a.oper_key_no,null) as oper_key_no,\n" +
                    "       b.frname,\n" +
                    "       concat(b.regcap,\"万\",b.regcapcur) as reg,\n" +
                    "       b.REGCAP,\n" +
                    "       b.REGCAPCUR,\n" +
                    "       '',\n" +
                    "       b.ENTSTATUS,\n" +
                    "       b.ESDATE,\n" +
                    "       b.REVDATE,\n" +
                    "       b.CREDITCODE,\n" +
                    "       b.REGNO,\n" +
                    "       b.ENTTYPE,\n" +
                    "       b.ENTTYPECODE,\n" +
                    "       b.APPRDATE,\n" +
                    "       b.REGORG,\n" +
                    "       b.S_EXT_NODENUM,\n" +
                    "       b.REGORGPROVINCE,\n" +
                    "       b.OPFROM,\n" +
                    "       b.OPTO,\n" +
                    "       b.DOM,\n" +
                    "       b.ZSOPSCOPE,\n" +
                    "       a.phone_number,\n" +
                    "       substr(current_timestamp(),1,19) as updated_date,\n" +
                    "       b.JOBID,\n" +
                    "       \"0\" as isadd, " +
                    "a.tax_no," +
                    "b.ORGCODES," +
                    "b.REGORGCITY," +
                    "b.REGORGDISTRICT," +
                    "if(regexp_replace(trim(a.company_name),'([(（）)])','') == regexp_replace(trim(b.ENTNAME),'([(（）)])',''),a.english_name,'') as english_name," +
                    "b.ENTNAME_OLD," +
                    "a.image_url," +
                    "a.email," +
                    "       b.industryphy,\n" +
                    "       b.industryphyname,\n" +
                    "       if(length(trim(b.industryco))=2,b.industryco,'') as sub_industry_code,\n" +
                    "       if(length(trim(b.industryco))=2,b.industryconame,'') as sub_industry,\n" +
                    "       if(length(trim(b.industryco))=3,b.industryco,'') as middle_category_code,\n" +
                    "       if(length(trim(b.industryco))=3,b.industryconame,'') as middle_category,\n" +
                    "       if(length(trim(b.industryco))=4,b.industryco,'') as small_category_code,\n" +
                    "       if(length(trim(b.industryco))=4,b.industryconame,'') as small_category,\n" +
                    "a.website,b.LON,b.LAT " +
                    "FROM\n" +
                    "  (SELECT *\n" +
                    "   FROM zs_company\n" +
                    "   WHERE creditcode IS NOT NULL\n" +
                    "   AND creditcode !='') b\n" +
                    "INNER JOIN\n" +
                    "   (select aa.* from tmp_sum_company aa where length(aa.credit_code)=18) a ON upper(trim(a.credit_code)) = upper(trim(b.creditcode))").createOrReplaceTempView("t_company1");

            spark.sql("SELECT b.entid as key_no,\n" +
                    " b.entid," +
                    "       a.company_id,\n" +
                    "       b.entname,\n" +
                    "       if(trim(b.frname) == trim(a.oper_name),a.oper_key_no,null) as oper_key_no,\n" +
                    "       b.frname,\n" +
                    "       concat(b.regcap,\"万\",b.regcapcur) as reg,\n" +
                    "       b.REGCAP,\n" +
                    "       b.REGCAPCUR,\n" +
                    "       '',\n" +
                    "       b.ENTSTATUS,\n" +
                    "       b.ESDATE,\n" +
                    "       b.REVDATE,\n" +
                    "       if(length(trim(b.creditcode))!=0,b.creditcode,a.credit_code) as creditcode,\n" +
                    "       b.REGNO,\n" +
                    "       b.ENTTYPE,\n" +
                    "       b.ENTTYPECODE,\n" +
                    "       b.APPRDATE,\n" +
                    "       b.REGORG,\n" +
                    "       b.S_EXT_NODENUM,\n" +
                    "       b.REGORGPROVINCE,\n" +
                    "       b.OPFROM,\n" +
                    "       b.OPTO,\n" +
                    "       b.DOM,\n" +
                    "       b.ZSOPSCOPE,\n" +
                    "       a.phone_number,\n" +
                    "       substr(current_timestamp(),1,19) as updated_date,\n" +
                    "       b.JOBID,\n" +
                    "       \"0\" AS isadd,\n" +
                    "a.tax_no," +
                    "b.ORGCODES," +
                    "b.REGORGCITY," +
                    "b.REGORGDISTRICT," +
                    "if(regexp_replace(trim(a.company_name),'([(（）)])','') == regexp_replace(trim(b.ENTNAME),'([(（）)])',''),a.english_name,'') as english_name," +
                    "b.ENTNAME_OLD," +
                    "a.image_url," +
                    "a.email," +
                    "       b.industryphy,\n" +
                    "       b.industryphyname,\n" +
                    "       if(length(trim(b.industryco))=2,b.industryco,'') as sub_industry_code,\n" +
                    "       if(length(trim(b.industryco))=2,b.industryconame,'') as sub_industry,\n" +
                    "       if(length(trim(b.industryco))=3,b.industryco,'') as middle_category_code,\n" +
                    "       if(length(trim(b.industryco))=3,b.industryconame,'') as middle_category,\n" +
                    "       if(length(trim(b.industryco))=4,b.industryco,'') as small_category_code,\n" +
                    "       if(length(trim(b.industryco))=4,b.industryconame,'') as small_category,\n" +
                    "a.website,b.LON,b.LAT " +
                    "FROM\n" +
                    "  (SELECT u.*\n" +
                    "   FROM zs_company u\n" +
                    " left join t_company1 uu on u.entid = uu.entid " +
                    "   WHERE uu.entid is null and u.regno is not null and u.regno !='') b\n" +
                    "INNER JOIN\n" +
                    "  (select aaa.* from (select aa.*,ROW_NUMBER() OVER(PARTITION BY aa.no,aa.company_name ORDER BY aa.dates DESC,aa.isadd DESC) num from " +
                    "(select op.* from tmp_sum_company as op where length(op.no)>=13 and length(op.no) < 18) aa ) aaa where aaa.num = 1) a " +
                    "ON regexp_replace(trim(a.no),'-','') = regexp_replace(trim(b.regno),'-','') " +
                    "and regexp_replace(trim(a.company_name),'([(（）)])','') = regexp_replace(trim(b.ENTNAME),'([(（）)])','')").createOrReplaceTempView("t_company2");

            spark.sql("SELECT b.entid as key_no,\n" +
                    " b.entid," +
                    "       a.company_id,\n" +
                    "       b.entname,\n" +
                    "       if(trim(b.frname) == trim(a.oper_name),a.oper_key_no,null) as oper_key_no,\n" +
                    "       b.frname,\n" +
                    "       concat(b.regcap,\"万\",b.regcapcur) as reg,\n" +
                    "       b.REGCAP,\n" +
                    "       b.REGCAPCUR,\n" +
                    "       '',\n" +
                    "       b.ENTSTATUS,\n" +
                    "       b.ESDATE,\n" +
                    "       b.REVDATE,\n" +
                    "       if(length(trim(b.creditcode))!=0,b.creditcode,a.credit_code) as creditcode,\n" +
                    "       b.REGNO,\n" +
                    "       b.ENTTYPE,\n" +
                    "       b.ENTTYPECODE,\n" +
                    "       b.APPRDATE,\n" +
                    "       b.REGORG,\n" +
                    "       b.S_EXT_NODENUM,\n" +
                    "       b.REGORGPROVINCE,\n" +
                    "       b.OPFROM,\n" +
                    "       b.OPTO,\n" +
                    "       b.DOM,\n" +
                    "       b.ZSOPSCOPE,\n" +
                    "       a.phone_number,\n" +
                    "       substr(current_timestamp(),1,19) as updated_date,\n" +
                    "       b.JOBID,\n" +
                    "       \"0\" AS isadd,\n" +
                    "a.tax_no," +
                    "b.ORGCODES," +
                    "b.REGORGCITY," +
                    "b.REGORGDISTRICT," +
                    "if(regexp_replace(trim(a.company_name),'([(（）)])','') == regexp_replace(trim(b.ENTNAME),'([(（）)])',''),a.english_name,'') as english_name," +
                    "b.ENTNAME_OLD," +
                    "a.image_url," +
                    "a.email," +
                    "       b.industryphy,\n" +
                    "       b.industryphyname,\n" +
                    "       if(length(trim(b.industryco))=2,b.industryco,'') as sub_industry_code,\n" +
                    "       if(length(trim(b.industryco))=2,b.industryconame,'') as sub_industry,\n" +
                    "       if(length(trim(b.industryco))=3,b.industryco,'') as middle_category_code,\n" +
                    "       if(length(trim(b.industryco))=3,b.industryconame,'') as middle_category,\n" +
                    "       if(length(trim(b.industryco))=4,b.industryco,'') as small_category_code,\n" +
                    "       if(length(trim(b.industryco))=4,b.industryconame,'') as small_category,\n" +
                    "a.website,b.LON,b.LAT " +
                    "FROM\n" +
                    "  (SELECT u.*\n" +
                    "   FROM zs_company u " +
                    "left join t_company1 uu on u.entid = uu.entid " +
                    "left join t_company2 uuu on uu.entid = uuu.entid and u.entid = uuu.entid " +
                    "   where uu.entid is null and uuu.entid is null) b\n" +
                    " INNER JOIN\n" +
                    " tmp_sum_company a " +
                    "ON regexp_replace(trim(a.company_name),'([(（）)])','') = regexp_replace(trim(b.ENTNAME),'([(（）)])','') " +
                    "and regexp_replace(substr(cast(b.ESDATE as string),1,10),'([^0-9]+)','') = regexp_replace(substr(cast(a.start_date as string),1,10),'([^0-9]+)','') " +
                    "and trim(a.oper_name) = trim(b.FRNAME)").createOrReplaceTempView("t_company3");

            spark.sql("select distinct a.* from (select * from t_company1 union all select * from t_company2 union all select * from t_company3) a").createOrReplaceTempView("tmp_t_eci_company");
            spark.catalog().dropGlobalTempView("t_company1");
            spark.catalog().dropGlobalTempView("t_company2");
            spark.catalog().dropGlobalTempView("t_company3");
//            spark.sql("insert overwrite table a_dc_ep_incr.t_eci_companyattr select distinct key_no,company_id,entname,tax_no,ORGCODES,REGORGCITY,REGORGDISTRICT,english_name,ENTNAME_OLD,image_url,email,website,jobid,isadd from tmp_t_eci_company");
            spark.sql("insert overwrite table a_dc_ep_incr.t_eci_company select  distinct " +
                    "       b.key_no,\n" +
                    "       b.company_id,\n" +
                    "       b.entname,\n" +
                    "       b.oper_key_no,\n" +
                    "       b.frname,\n" +
                    "       b.reg,\n" +
                    "       b.REGCAP,\n" +
                    "       b.REGCAPCUR,\n" +
                    "       '',\n" +
                    "       b.ENTSTATUS,\n" +
                    "       b.ESDATE,\n" +
                    "       b.REVDATE,\n" +
                    "       b.CREDITCODE,\n" +
                    "       b.REGNO,\n" +
                    "       b.ENTTYPE,\n" +
                    "       b.ENTTYPECODE,\n" +
                    "       b.APPRDATE,\n" +
                    "       b.REGORG,\n" +
                    "       b.S_EXT_NODENUM,\n" +
                    "       b.REGORGPROVINCE,\n" +
                    "       b.OPFROM,\n" +
                    "       b.OPTO,\n" +
                    "       b.DOM,\n" +
                    "       b.ZSOPSCOPE,\n" +
                    "       b.phone_number,\n" +
                    "       substr(current_timestamp(),1,19) as updated_date,\n" +
                    "       b.tax_no," +
                    "       b.ORGCODES," +
                    "       b.REGORGCITY," +
                    "       b.REGORGDISTRICT," +
                    "       b.english_name," +
                    "       b.ENTNAME_OLD," +
                    "       b.image_url," +
                    "       b.email," +
                    "       b.website," +
                    "       b.LON," +
                    "       b.LAT, " +
                    "       b.industryphy,\n" +
                    "       b.industryphyname,\n" +
                    "       b.sub_industry_code,\n" +
                    "       b.sub_industry,\n" +
                    "       b.middle_category_code,\n" +
                    "       b.middle_category,\n" +
                    "       b.small_category_code,\n" +
                    "       b.small_category,\n" +
                    "       b.JOBID," +
                    "       b.isadd" +
                    " from tmp_t_eci_company b");
            //获取中数增量数据表中的新增数据（left join）
            Dataset<Row> insert_data = spark.sql("SELECT b.entid as key_no,\n" +
                    "       md5(if(length(trim(b.creditcode))=18,trim(b.creditcode),trim(b.entname))) as company_id,\n" +
                    "       b.entname,\n" +
                    "       null as oper_key_no,\n" +
                    "       b.frname,\n" +
                    "       concat(b.regcap,\"万\",b.regcapcur) as reg,\n" +
                    "       b.REGCAP,\n" +
                    "       b.REGCAPCUR,\n" +
                    "       '',\n" +
                    "       b.ENTSTATUS,\n" +
                    "       b.ESDATE,\n" +
                    "       b.REVDATE,\n" +
                    "       b.CREDITCODE,\n" +
                    "       b.REGNO,\n" +
                    "       b.ENTTYPE,\n" +
                    "       b.ENTTYPECODE,\n" +
                    "       b.APPRDATE,\n" +
                    "       b.REGORG,\n" +
                    "       b.S_EXT_NODENUM,\n" +
                    "       b.REGORGPROVINCE,\n" +
                    "       b.OPFROM,\n" +
                    "       b.OPTO,\n" +
                    "       b.DOM,\n" +
                    "       b.ZSOPSCOPE,\n" +
                    "       a. phone_number,\n" +
                    "       substr(current_timestamp(),1,19) as updated_date,\n" +
                    "       b.JOBID,\n" +
                    "       \"1\" as isadd,\n" +
                    "a.tax_no, " +
                    "b.ORGCODES," +
                    "b.REGORGCITY," +
                    "b.REGORGDISTRICT," +
                    "a.english_name," +
                    "b.ENTNAME_OLD," +
                    "a.image_url," +
                    "a.email," +
                    "       b.industryphy,\n" +
                    "       b.industryphyname,\n" +
                    "       if(length(trim(b.industryco))=2,b.industryco,'') as sub_industry_code,\n" +
                    "       if(length(trim(b.industryco))=2,b.industryconame,'') as sub_industry,\n" +
                    "       if(length(trim(b.industryco))=3,b.industryco,'') as middle_category_code,\n" +
                    "       if(length(trim(b.industryco))=3,b.industryconame,'') as middle_category,\n" +
                    "       if(length(trim(b.industryco))=4,b.industryco,'') as small_category_code,\n" +
                    "       if(length(trim(b.industryco))=4,b.industryconame,'') as small_category,\n" +
                    "a.website,b.LON,b.LAT " +
                    "FROM\n" +
                    "  zs_company b\n" +
                    "LEFT JOIN\n" +
                    "  tmp_t_eci_company a ON b.entid = a.entid\n" +
                    "WHERE a.entid IS NULL");
//        Dataset<Row> insert_data = spark.sql("SELECT md5(if(length(b.creditcode)=18,b.creditcode,b.entname)) as key_no,\n" +
//                "       md5(if(length(b.creditcode)=18,b.creditcode,b.entname)) as company_id,\n" +
//                "       b.entname,\n" +
//                "       '智安信取数据' as oper_key_no,\n" +
//                "       b.frname,\n" +
//                "       concat(b.regcap,\"万\",b.regcapcur) as reg,\n" +
//                "       b.REGCAP,\n" +
//                "       b.REGCAPCUR,\n" +
//                "       b.REGCAP,\n" +
//                "       b.ENTSTATUS,\n" +
//                "       b.ESDATE,\n" +
//                "       b.REVDATE,\n" +
//                "       b.CREDITCODE,\n" +
//                "       b.REGNO,\n" +
//                "       b.ENTTYPE,\n" +
//                "       b.ENTTYPECODE,\n" +
//                "       b.APPRDATE,\n" +
//                "       b.REGORG,\n" +
//                "       b.S_EXT_NODENUM,\n" +
//                "       b.REGORGPROVINCE,\n" +
//                "       b.OPFROM,\n" +
//                "       b.OPTO,\n" +
//                "       b.DOM,\n" +
//                "       b.ZSOPSCOPE,\n" +
//                "       '' as phone_number,\n" +
//                "       a.updated_date,\n" +
//                "       b.JOBID,\n" +
//                "       \"1\" as isadd,\n" +
//                "a.tax_no,b.ORGCODES," +
//                "b.REGORGCITY," +
//                "b.REGORGDISTRICT," +
//                "a.english_name," +
//                "b.ENTNAME_OLD," +
//                "a.image_url," +
//                "a.email," +
//                "a.website\n " +
//                "FROM\n" +
//                "  (SELECT *\n" +
//                "   FROM zs_company \n" +
//                "   WHERE creditcode IS NOT NULL\n" +
//                "     AND creditcode !='') b\n" +
//                "LEFT JOIN\n" +
//                "  (SELECT *\n" +
//                "   FROM tmp_sum_company\n" +
//                "   WHERE credit_code IS NOT NULL\n" +
//                "     AND credit_code!='') a ON upper(trim(a.credit_code)) = upper(trim(b.creditcode))\n" +
//                "WHERE a.credit_code IS NULL").union(spark.sql("SELECT md5(b.entname) as key_no,\n" +
//                "       md5(b.entname) as company_id,\n" +
//                "       b.entname,\n" +
//                "       '智安信取数据' AS oper_key_no,\n" +
//                "       b.frname,\n" +
//                "       concat(b.regcap,\"万\",b.regcapcur) as reg,\n" +
//                "       b.REGCAP,\n" +
//                "       b.REGCAPCUR,\n" +
//                "       b.REGCAP,\n" +
//                "       b.ENTSTATUS,\n" +
//                "       b.ESDATE,\n" +
//                "       b.REVDATE,\n" +
//                "       a.credit_code,\n" +
//                "       b.REGNO,\n" +
//                "       b.ENTTYPE,\n" +
//                "       b.ENTTYPECODE,\n" +
//                "       b.APPRDATE,\n" +
//                "       b.REGORG,\n" +
//                "       b.S_EXT_NODENUM,\n" +
//                "       b.REGORGPROVINCE,\n" +
//                "       b.OPFROM,\n" +
//                "       b.OPTO,\n" +
//                "       b.DOM,\n" +
//                "       b.ZSOPSCOPE,\n" +
//                "       '' AS phone_number,\n" +
//                "       a.updated_date,\n" +
//                "       b.JOBID,\n" +
//                "       \"1\" AS isadd,\n" +
//                "a.tax_no,b.ORGCODES," +
//                "b.REGORGCITY," +
//                "b.REGORGDISTRICT," +
//                "a.english_name," +
//                "b.ENTNAME_OLD," +
//                "a.image_url," +
//                "a.email," +
//                "a.website\n " +
//                "FROM\n" +
//                "  (SELECT *\n" +
//                "   FROM zs_company \n" +
//                "   WHERE regno IS NOT NULL\n" +
//                "     AND regno !=''\n" +
//                "     AND (creditcode IS NULL\n" +
//                "          OR creditcode = '')) b\n" +
//                "LEFT JOIN\n" +
//                "  (SELECT *\n" +
//                "   FROM tmp_sum_company\n" +
//                "   WHERE NO IS NOT NULL\n" +
//                "     AND NO!='') a ON regexp_replace(trim(a.no),'-','') = regexp_replace(trim(b.regno),'-','')\n" +
//                "WHERE a.no IS NULL"));
            spark.catalog().dropGlobalTempView("tmp_t_eci_company");
            insert_data.createOrReplaceTempView("tmp_t_eci_company1");
//            spark.sql("insert into table a_dc_ep_incr.t_eci_companyattr select distinct key_no,company_id,entname,tax_no,ORGCODES,REGORGCITY,REGORGDISTRICT,english_name,ENTNAME_OLD,image_url,email,website,jobid,isadd from tmp_t_eci_company1");
            spark.sql("insert into table a_dc_ep_incr.t_eci_company select distinct " +
                    "       b.key_no,\n" +
                    "       b.company_id,\n" +
                    "       b.entname,\n" +
                    "       b.oper_key_no,\n" +
                    "       b.frname,\n" +
                    "       b.reg,\n" +
                    "       b.REGCAP,\n" +
                    "       b.REGCAPCUR,\n" +
                    "       '',\n" +
                    "       b.ENTSTATUS,\n" +
                    "       b.ESDATE,\n" +
                    "       b.REVDATE,\n" +
                    "       b.CREDITCODE,\n" +
                    "       b.REGNO,\n" +
                    "       b.ENTTYPE,\n" +
                    "       b.ENTTYPECODE,\n" +
                    "       b.APPRDATE,\n" +
                    "       b.REGORG,\n" +
                    "       b.S_EXT_NODENUM,\n" +
                    "       b.REGORGPROVINCE,\n" +
                    "       b.OPFROM,\n" +
                    "       b.OPTO,\n" +
                    "       b.DOM,\n" +
                    "       b.ZSOPSCOPE,\n" +
                    "       b.phone_number,\n" +
                    "       b.updated_date,\n" +
                    "       b.tax_no," +
                    "       b.ORGCODES," +
                    "       b.REGORGCITY," +
                    "       b.REGORGDISTRICT," +
                    "       b.english_name," +
                    "       b.ENTNAME_OLD," +
                    "       b.image_url," +
                    "       b.email," +
                    "       b.website," +
                    "       b.LON," +
                    "       b.LAT," +
                    "       b.industryphy,\n" +
                    "       b.industryphyname,\n" +
                    "       b.sub_industry_code,\n" +
                    "       b.sub_industry,\n" +
                    "       b.middle_category_code,\n" +
                    "       b.middle_category,\n" +
                    "       b.small_category_code,\n" +
                    "       b.small_category,\n" +
                    "       b.JOBID,\n" +
                    "       b.isadd\n" +
                    " from tmp_t_eci_company1 b");
            spark.catalog().dropGlobalTempView("tmp_t_eci_company1");
            //获取中数删除数据表中的数据
            Dataset<Row> delete_data = spark.sql("select a.key_no,a.company_id,a.company_name," +
                    "a.oper_key_no,a.oper_name,a.regist_capi,a.regist_capi_value,a.regist_capi_unit," +
                    "a.rec_cap,a.status,a.start_date,a.end_date,a.credit_code,a.no,a.econ_kind,a.econ_kind_code,a.check_date," +
                    "a.belong_org,a.province_code,a.province,a.term_start,a.term_end,a.address,a.scope,a.phone_number,a.updated_date," +
                    "a.tax_no, a.org_no,a.city,a.county,a.english_name,a.original_name,a.image_url,a.email,a.website,a.lat,a.lng," +
                    "a.industryphy,a.industryphyname,a.sub_industry_code,a.sub_industry,a.middle_category_code,a.middle_category," +
                    "a.small_category_code,a.small_category,a.dates,'-1' as isadd " +
                    "from tmp_sum_company a inner join (select del.* from a_dc_ep_ods.zs_t_ent_basic_del del " +
                    "left join zs_company basic on del.entid = basic.entid where basic.entid is null and del.creditcode is not null and del.creditcode !='') " +
                    "b on upper(trim(a.credit_code)) = upper(trim(b.creditcode))");
            delete_data.createOrReplaceTempView("tmp_t_eci_company2");
            spark.sql("insert into table a_dc_ep_incr.t_eci_company select distinct * from tmp_t_eci_company2");
            //t_eci_companyattr删除数据
//            Dataset<Row> companyattr_delete_data = spark.sql("select a.key_no,a.company_id,a.company_name,a.tax_no,a.org_no,a.city," +
//                    "a.county,a.english_name,a.original_name,a.image_url,a.email,a.website,a.dates,'-1' as isadd " +
//                    "from tmp_sum_company a inner join (select del.* from a_dc_ep_ods.zs_t_ent_basic_del del " +
//                    "left join zs_company basic on del.entid = basic.entid where basic.entid is null and del.creditcode is not null and del.creditcode !='') " +
//                    "b on upper(trim(a.credit_code)) = upper(trim(b.creditcode))");
//            companyattr_delete_data.createOrReplaceTempView("companyattr_delete_data");
//            spark.sql("insert into table a_dc_ep_incr.t_eci_companyattr select distinct * from companyattr_delete_data");
            spark.catalog().dropGlobalTempView("tmp_t_eci_company2");
//            spark.catalog().dropGlobalTempView("companyattr_delete_data");
        }else {
            spark.sql("TRUNCATE TABLE a_dc_ep_incr.t_eci_company");
//            spark.sql("TRUNCATE TABLE a_dc_ep_incr.t_eci_companyattr");
            System.out.println("中数本期数据涉及该表的数据为空。。。。。。");
        }
        spark.stop();
    }
}
